-- @owner: @li-xin12345
-- @date: 2023/8/3
-- @testpoint: 间隔分区表，非压缩表改为压缩表
-- @history: Modified by li-xin12345 2025.3.24：研发代码变更，压缩率变化

--step1:建间隔分区表并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0019 cascade;
create table t_alter_rowcompress_0019
(prod_id number(6),
 cust_id number,
 time_id date,
 channel_id char(1),
 promo_id number(6),
 quantity_sold number(3),
 amount_sold number(10,2)
)
partition by range (time_id)
interval('1 day')
( partition t_alter_rowcompress_0019_01 values less than ('2023-02-01 00:00:00'),
  partition t_alter_rowcompress_0019_02 values less than ('2023-02-02 00:00:00')
);

insert into t_alter_rowcompress_0019 values(generate_series(0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-01-10 00:00:00', 'a', 1, 1, 1);
insert into t_alter_rowcompress_0019 values(generate_series(0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-02-01 00:00:00', 'a', 2, 2, 2);

--step2:查看表文件占用磁盘大小;expect:成功
select pg_size_pretty(pg_total_relation_size('t_alter_rowcompress_0019')) as table_size;

--step3:修改表为压缩表，checkpoint;expect:成功
alter table t_alter_rowcompress_0019 set (compresstype = 1);
checkpoint;

--step4:再次查看表文件占用磁盘大小;expect:表文件磁盘占用小于非压缩表
select pg_size_pretty(pg_total_relation_size('t_alter_rowcompress_0019')) as table_size;

--step5:查看压缩率;expect:成功
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/

select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0019'));

--step6:查看分区表信息;expect:成功
select relname, reloptions from pg_partition where relname like '%rowcompress_0019%' order by relname;

--step7:清理环境;expect:成功
drop table if exists t_alter_rowcompress_0019;
drop function if exists compress_func_findpath(character varying);